package org.footito.android.data.database;

import java.util.GregorianCalendar;

import org.footito.android.tools.Log;
import org.footito.android.tools.Settings;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

class FootitoDatabase extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "footito";

	/* here goes my constants: @formatter:off */

	/* Informations for table Teams */
	public static final String TABLE_TEAMS = "teams";
	public static final String COL_TEAM_ID = "_id";
	public static final String COL_TEAM_NAME = "team_name";
	public static final String COL_TEAM_LINK = "team_link";
	public static final String COL_TEAM_PICTURE = "team_picture";
	public static final String COL_TEAM_GROUPID = "team_groupid";
	public static final int COLNUM_TEAM_ID = 0;
	public static final int COLNUM_TEAM_NAME = 1;
	public static final int COLNUM_TEAM_LINK = 2;
	public static final int COLNUM_TEAM_PICTURE = 3;
	public static final int COLNUM_TEAM_GROUPID = 4;
	private static final String CREATE_TABLE_TEAMS = "CREATE TABLE " + TABLE_TEAMS + "( " 
			+ COL_TEAM_ID + " integer primary key, " 
			+ COL_TEAM_NAME	+ " TEXT NOT NULL, " 
			+ COL_TEAM_LINK + " TEXT NOT NULL, " 
			+ COL_TEAM_PICTURE + " TEXT NOT NULL, " 
			+ COL_TEAM_GROUPID + " integer NOT NULL);";
	public static final String REQUEST_ONE_TEAM = COL_TEAM_ID + " = ?";

	/* Informations for table TeamGroups */
	public static final String TABLE_TEAMGROUPS = "team_groups";
	public static final String COL_TEAMGROUPS_ID = "_id";
	public static final String COL_TEAMGROUPS_NAME = "name";
	public static final int COLNUM_TEAMGROUPS_ID = 0;
	public static final int COLNUM_TEAMGROUPS_NAME = 1;
	private static final String CREATE_TABLE_TEAMGROUPS = "CREATE TABLE " + TABLE_TEAMGROUPS + "( " 
			+ COL_TEAMGROUPS_ID + " integer primary key, " 
			+ COL_TEAMGROUPS_NAME + " TEXT NOT NULL);";

	/* Informations for table Status */
	public static final String TABLE_STATUS = "status";
	public static final String COL_STATUS_ID = "_id";
	public static final String COL_STATUS_IDTEAM = "status_idTeam";
	public static final String COL_STATUS_TIMESTAMP = "status_timestamp";
	public static final String COL_STATUS_CONTENT = "status_content";
	public static final String COL_STATUS_LINK = "status_link";
	public static final String COL_STATUS_INSERTION = "status_insertion_timestamp";
	public static final String COL_STATUS_PLIKE = "status_plike";
	public static final String COL_STATUS_NLIKE = "status_nlike";
	public static final int COLNUM_STATUS_ID = 0;
	public static final int COLNUM_STATUS_IDTEAM = 1;
	public static final int COLNUM_STATUS_TIMESTAMP = 2;
	public static final int COLNUM_STATUS_CONTENT = 3;
	public static final int COLNUM_STATUS_LINK = 4;
	public static final int COLNUM_STATUS_INSERTION = 5;
	public static final int COLNUM_STATUS_PLIKE = 6;
	public static final int COLNUM_STATUS_NLIKE = 7;
	private static final String CREATE_TABLE_STATUS = "CREATE TABLE " + TABLE_STATUS + "( " 
			+ COL_STATUS_ID + " integer primary key, " 
			+ COL_STATUS_IDTEAM + " integer NOT NULL, "
			+ COL_STATUS_TIMESTAMP + " integer NOT NULL, " 
			+ COL_STATUS_CONTENT + " TEXT NOT NULL, " 
			+ COL_STATUS_LINK + " TEXT NOT NULL, " 
			+ COL_STATUS_INSERTION + " TEXT NOT NULL, " 
			+ COL_STATUS_PLIKE + " integer NOT NULL, " 
			+ COL_STATUS_NLIKE + " integer NOT NULL);";
	public static final String REQUEST_ONE_STATUS = COL_STATUS_ID + " = ?";
	public static final String REQUEST_STATUS_BY_TEAM = COL_STATUS_IDTEAM + " = ?";
	public static final String REQUEST_STATUS_BY_TIMESTAMP = COL_STATUS_TIMESTAMP + " < ?";
	public static final String REQUEST_STATUS_BY_INSERTION_BEFORE = COL_STATUS_INSERTION + " < ?";
	public static final String REQUEST_STATUS_BY_INSERTION_AFTER = COL_STATUS_INSERTION + " > ?";

	/* Informations for table Comments */
	public static final String TABLE_COMMENTS = "comments";
	public static final String COL_COMMENT_ID = "_id";
	public static final String COL_COMMENT_IDSTATUS = "comment_idStatus";
	public static final String COL_COMMENT_IDTEAM = "comment_idTeam";
	public static final String COL_COMMENT_CONTENT = "comment_content";
	public static final String COL_COMMENT_PLIKE = "comment_plike";
	public static final String COL_COMMENT_NLIKE = "comment_nlike";
	public static final int COLNUM_COMMENT_ID = 0;
	public static final int COLNUM_COMMENT_IDSTATUS = 1;
	public static final int COLNUM_COMMENT_CONTENT = 2;
	public static final int COLNUM_COMMENT_IDTEAM = 3;
	public static final int COLNUM_COMMENT_PLIKE = 4;
	public static final int COLNUM_COMMENT_NLIKE = 5;
	private static final String CREATE_TABLE_COMMENTS = "CREATE TABLE " + TABLE_COMMENTS + "( " 
			+ COL_COMMENT_ID + " integer primary key, " 
			+ COL_COMMENT_IDSTATUS + " integer NOT NULL, " 
			+ COL_COMMENT_CONTENT + " TEXT NOT NULL, " 
			+ COL_COMMENT_IDTEAM + " integer NOT NULL, " 
			+ COL_COMMENT_PLIKE + " integer NOT NULL, " 
			+ COL_COMMENT_NLIKE + " integer NOT NULL);";
	public static final String REQUEST_COMS_BY_STATUS = COL_COMMENT_IDSTATUS + " = ?";
	public static final String REQUEST_ONE_COMMENT = COL_COMMENT_ID + " = ?";
	
	/* @formatter:on */

	private static final String TAG = "DBAdapter: ";

	private static final int DATABASE_VERSION = Settings.CHECKVERSION;

	private Context context;
	private SQLiteDatabase db;

	FootitoDatabase(Context ctx) {
		super(ctx, DATABASE_NAME, null, DATABASE_VERSION);
		context = ctx;

		Log.v(TAG + "curDbVersion = " + DATABASE_VERSION);
	}

	public void close() {
		db.close();
	}

	public Cursor getAllCommentsCursor(String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
		return db.query(TABLE_COMMENTS, projection, selection, selectionArgs, null, null,
				COL_COMMENT_ID + " ASC");
	}

	public Cursor getAllStatusCursor(String[] projection, String selection, String[] selectionArgs,
			String sortOrder) {
		return db.query(TABLE_STATUS, projection, selection, selectionArgs, null, null,
				COL_STATUS_TIMESTAMP + " DESC");
	}

	public Cursor getStatusByPageCursor(String[] projection, String selection,
			String[] selectionArgs, String sortOrder, String limit, String offset) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT ");
		if (projection != null)
			query.append(projection);
		else
			query.append("*");
		query.append(" FROM ");
		query.append(TABLE_STATUS);
		if (selection != null) {
			query.append(" WHERE ");
			query.append(selection);
		}
		if (sortOrder != null) {
			query.append(" ORDER BY ");
			query.append(sortOrder);
			query.append(" ASC");
		} else {
			query.append(" ORDER BY ");
			query.append(COL_STATUS_TIMESTAMP);
			query.append(" DESC");
		}
		if (limit != null) {
			query.append(" LIMIT ");
			query.append(limit);
		}
		if (offset != null) {
			query.append(" OFFSET ");
			query.append(offset);
		}

//		Log.d(query.toString());
		return db.rawQuery(query.toString(), selectionArgs);
	}

	public Cursor getAllTeamsCursor(String[] projection, String selection, String[] selectionArgs,
			String sortOrder) {
		return db.query(TABLE_TEAMS, projection, selection, selectionArgs, null, null, COL_TEAM_ID
				+ " DESC");
	}

	public Cursor getAllTeamGroupsCursor(String[] projection, String selection,
			String[] selectionArgs, String sortOrder) {
		return db.query(TABLE_TEAMGROUPS, projection, selection, selectionArgs, null, null,
				COL_TEAMGROUPS_ID + " DESC");
	}

	public Cursor getCommentCursor(int id) {
		String selection = REQUEST_ONE_COMMENT;
		String[] selectionArgs = { Integer.toString(id) };
		return db.query(TABLE_COMMENTS, null, selection, selectionArgs, null, null, COL_COMMENT_ID
				+ " ASC");
	}

	public Cursor getStatusCursor(long id) {
		String selection = REQUEST_ONE_STATUS;
		String[] selectionArgs = { Long.toString(id) };
		return db.query(TABLE_STATUS, null, selection, selectionArgs, null, null, null);
	}

	public Cursor getLastStatusCursor() {
		String limit = "1";
		String sortOrder = COL_STATUS_TIMESTAMP + " DESC";
		return db.query(TABLE_STATUS, null, null, null, null, null, sortOrder, limit);
	}

	public Cursor getFirstStatusCursor() {
		String limit = "1";
		String sortOrder = COL_STATUS_TIMESTAMP + " ASC";
		return db.query(TABLE_STATUS, null, null, null, null, null, sortOrder, limit);
	}
	
	//FONCTION INTERNE
	public Cursor getStatusModererCursor() {
		String limit = "20";
		String sortOrder = COL_STATUS_TIMESTAMP + " ASC";
		return db.query(TABLE_STATUS, null, null, null, null, null, sortOrder, limit);
	}

	public Cursor getTeamCursor(int id) {
		String selection = REQUEST_ONE_TEAM;
		String[] selectionArgs = { Integer.toString(id) };
		return db.query(TABLE_TEAMS, null, selection, selectionArgs, null, null, null);
	}

	public long insertComment(ContentValues values) {
		return db.insert(TABLE_COMMENTS, null, values);
	}

	public long insertStatus(ContentValues values) {
		GregorianCalendar time = new GregorianCalendar();
		String timeS = Long.toString(time.getTimeInMillis());
		values.put(COL_STATUS_INSERTION, timeS);
		return db.insert(TABLE_STATUS, null, values);
	}

	public long insertTeam(ContentValues values) {
		try {
			return db.insert(TABLE_TEAMS, null, values);
		} catch (Exception e) {
			Log.e("Exception: " + e.getClass().toString() + ": " + e.getMessage());
			return -1;
		}
	}

	public boolean isOpen() {
		if (db == null)
			return false;
		return db.isOpen();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_TEAMGROUPS);
		db.execSQL(CREATE_TABLE_TEAMS);
		db.execSQL(CREATE_TABLE_STATUS);
		db.execSQL(CREATE_TABLE_COMMENTS);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG + "Upgrading database from version " + oldVersion + " to " + newVersion);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAMS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_STATUS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAMGROUPS);
		Settings.resetChecks(context);
		onCreate(db);
	}

	public void open() {
		// create the dbHelper
		if (db != null && db.isOpen()) {
			return;
		}
		db = this.getWritableDatabase();
	}

	public int removeComment(int id) {
		return db.delete(TABLE_COMMENTS, COL_COMMENT_ID + " = " + id, null);
	}

	public int removeComments(String selection, String[] selectionArgs) {
		return db.delete(TABLE_COMMENTS, selection, selectionArgs);
	}

	public int removeStatus(int id) {
		return db.delete(TABLE_STATUS, COL_STATUS_ID + " = " + id, null);
	}

	public int removeStatus(String where, String[] whereArgs) {
		return db.delete(TABLE_STATUS, where, whereArgs);
	}

	public int removeTeam(int id) {
		return db.delete(TABLE_TEAMS, COL_TEAM_ID + " = " + id, null);
	}

	public int removeTeams(String selection, String[] selectionArgs) {
		return db.delete(TABLE_TEAMS, selection, selectionArgs);
	}

	public int updateComment(ContentValues values, String string, String[] selectionArgs) {
		return db.update(TABLE_COMMENTS, values, string, selectionArgs);
	}

	public int updateStatus(ContentValues values, String selection, String[] selectionArgs) {
		return db.update(TABLE_STATUS, values, selection, selectionArgs);
	}

	public int updateTeam(ContentValues values, String string, String[] selectionArgs) {
		return db.update(TABLE_TEAMS, values, string, selectionArgs);
	}

	public long insertTeamGroup(ContentValues values) {
		return db.insert(TABLE_TEAMGROUPS, null, values);
	}

}
